package com.mjgy.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mjgy.entity.BroadCastSignerEntity;

/**
* @Description: TODO
* @author quanlq
* @date 2018年9月15日
* @version V1.0
*/
public interface BroadCastSignerRepository extends JpaRepository<BroadCastSignerEntity, Long>, JpaSpecificationExecutor<BroadCastSignerEntity> {

	void deleteBySignerId(Long signerId);
	
	@SuppressWarnings("rawtypes")
	@Query(value = "SELECT eu.id " +
			",eu.nickname " +
			",eu.avatar " +
			",bcs.is_dated " +
			"FROM e_user eu LEFT JOIN broast_cast_signer bcs ON eu.id = bcs.signer_id " +
			"WHERE bcs.broad_cast_id = :broadCastId LIMIT :page, :size", nativeQuery = true)
	List findSignerList(@Param("broadCastId") Long broadCastId, @Param("page") Integer page, @Param("size") Integer size);
	
	void deleteBybroadCastId(Long broadCastId);
	
	Integer countByBroadCastIdAndSignerId(Long broadCastId, Long signerId);
	
	BroadCastSignerEntity findTopByBroadCastIdAndSignerId(Long broadCastId, Long signerId);
	
	@Modifying
	@Transactional
	@Query(value = "UPDATE broast_cast_signer SET is_checked = 1 WHERE broad_cast_id = :broadCastId", nativeQuery = true)
	void checkBroadCastSigner(@Param("broadCastId") Long broadCastId);
	
	Integer countByBroadCastIdAndIsChecked(Long broadCastId, Integer isChecked);
	
	@Query(value = "SELECT COUNT(1) FROM broast_cast_signer bcs WHERE is_dated = 1 " +
			"AND (((SELECT id FROM broad_cast WHERE user_id = :activeId LIMIT 1) = bcs.broad_cast_id AND bcs.signer_id = :passiveId) OR " +
			"((SELECT id FROM broad_cast WHERE user_id = :passiveId LIMIT 1) = bcs.broad_cast_id AND bcs.signer_id = :activeId))", 
			nativeQuery = true)
	Integer isDated(@Param("activeId") Long activeId, @Param("passiveId") Long passiveId);
	
	Integer countByBroadCastIdAndSignerIdAndIsDated(Long broadCastId, Long signerId, Integer isDated);
	
}
 